package com.cater.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class InputExcelUtil {
	public static List<List<Object>> importExcel(File file) throws IOException {
		String fileName = file.getName();
		String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
		Workbook wb;
		if ("xls".equals(extension)) {
			FileInputStream fis = new FileInputStream(file); // 文件流对象
			wb = new HSSFWorkbook(fis);
		} else if ("xlsx".equals(extension)) {
			FileInputStream fi = new FileInputStream(file);
			wb = new XSSFWorkbook(fi);
		} else {
			throw new IOException("不支持的文件类型");
		}
		// 开始解析
		Sheet sheet = wb.getSheetAt(0); // 读取sheet 0

		int firstRowIndex = sheet.getFirstRowNum() + 1; // 第一行是列名，所以不读
		int lastRowIndex = sheet.getLastRowNum();
		List<List<Object>> list = new LinkedList<List<Object>>();
		for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { // 遍历行
			Row row = sheet.getRow(rIndex);
			List<Object> linked = new LinkedList<Object>();
			if (row != null) {
				if (row.getPhysicalNumberOfCells() > 0) {
					int firstCellIndex = row.getFirstCellNum();
					int lastCellIndex = row.getLastCellNum();
					for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) { // 遍历列
						Cell cell = row.getCell(cIndex);
						String value = "";
						if (cell != null) {
							switch (cell.getCellType()) {
							// 数值型
							case Cell.CELL_TYPE_NUMERIC:
								if (HSSFDateUtil.isCellDateFormatted(cell)) {
									// 如果是date类型则 ，获取该cell的date值
									Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
									SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
									value = format.format(date);
									;
								} else {// 纯数字
									BigDecimal big = new BigDecimal(cell.getNumericCellValue());
									value = big.toString();
									// 解决1234.0 去掉后面的.0
									if (null != value && !"".equals(value.trim())) {
										String[] item = value.split("[.]");
										if (1 < item.length && "0".equals(item[1])) {
											value = item[0];
										}
									}
								}
								break;
							// 字符串类型
							case Cell.CELL_TYPE_STRING:
								value = cell.getStringCellValue().toString();
								break;
							// 公式类型
							case Cell.CELL_TYPE_FORMULA:
								// 读公式计算值
								value = String.valueOf(cell.getNumericCellValue());
								if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
									value = cell.getStringCellValue().toString();
								}
								break;
							// 布尔类型
							case Cell.CELL_TYPE_BOOLEAN:
								value = " " + cell.getBooleanCellValue();
								break;
							// 空值
							case Cell.CELL_TYPE_BLANK:
								value = "";
								break;
							// 故障
							case Cell.CELL_TYPE_ERROR:
								value = "";
								break;
							default:
								value = cell.getStringCellValue().toString();
							}
							linked.add(value);
						}
					}
					list.add(linked);
				}

			}
		}
		return list;
	}
}
